﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using BCCommon.OAEnums.TeamWork;
using BCEntity.OA.TeamWork.TeamWork;
using BCCommon;

namespace BCData.OA.TeamWork.TeamWork
{
    /// <summary>
    /// 协同办公
    /// </summary>
    public class TeamWorkData : ITeamWorkData
    {
        private const string QUERY_EXISTS = @"SELECT 1 FROM OA_TeamWork";
        private const string QUERY = @"SELECT TeamWorkId,FinishAdminId,FinishAdminName,FinishRemark,CloseTime,CloseAdminId,CloseAdminName,CloseRemark,TeamWorkCategoryId,WorkTitle,
                                            WorkContent,WorkStatus,CreateAdminId,CreateAdminName,CreateTime,FinishTime,ReferenceNo,ThreadId
                                        FROM OA_TeamWork";
        private const string QUERY_VIEW = @"SELECT A.TeamWorkId,A.FinishAdminId,A.FinishAdminName,A.FinishRemark,A.CloseTime,A.CloseAdminId,A.CloseAdminName,A.CloseRemark,A.TeamWorkCategoryId,A.WorkTitle,
                                            A.WorkContent,A.WorkStatus,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.FinishTime,
                                            B.TeamWorkCategoryCode,B.TeamWorkCategoryName,A.ReferenceNo,A.ThreadId
                                        FROM OA_TeamWork A
                                        INNER JOIN OA_TeamWorkCategory B ON A.TeamWorkCategoryId=B.TeamWorkCategoryId";
        private const string QUERY_PARTICIPANT_VIEW = @"SELECT A.TeamWorkId,A.FinishAdminId,A.FinishAdminName,A.FinishRemark,A.CloseTime,A.CloseAdminId,A.CloseAdminName,A.CloseRemark,A.TeamWorkCategoryId,A.WorkTitle,
                                            A.WorkContent,A.WorkStatus,A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.FinishTime,
                                            B.TeamWorkCategoryCode,B.TeamWorkCategoryName,A.ReferenceNo,A.ThreadId
                                        FROM OA_TeamWork A
                                        INNER JOIN OA_TeamWorkCategory B ON A.TeamWorkCategoryId=B.TeamWorkCategoryId
                                        INNER JOIN OA_TeamWorkParticipant D ON A.TeamWorkId=D.TeamWorkId";

        private readonly IDatabaseContext databaseContext;

        public TeamWorkData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<bool> Exists(long teamWorkId)
        {
            string sql = string.Format(@"{0} WHERE TeamWorkId=?TeamWorkId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", teamWorkId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<TeamWorkEntity> Add(TeamWorkEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("OA_TeamWork", new string[] { "TeamWorkCategoryId", "WorkTitle", "WorkContent", "WorkStatus", "CreateAdminId", "CreateAdminName", "ReferenceNo" });
            sql = string.Format("{0} {1} WHERE TeamWorkId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkCategoryId", entity.TeamWorkCategoryId, DbType.Int32, size: 11);
            parameters.Add("?WorkTitle", entity.WorkTitle?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?WorkContent", entity.WorkContent?.Trim(), DbType.String);
            parameters.Add("?WorkStatus", OAWorkStatus.Progressing.ToString(), DbType.AnsiString);
            parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ReferenceNo", entity.ReferenceNo, DbType.AnsiString);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<TeamWorkEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<TeamWorkEntity> Update(TeamWorkEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("OA_TeamWork", new string[] { "FinishAdminId", "FinishAdminName", "FinishRemark", "CloseTime", "CloseAdminId", "CloseAdminName",
                "CloseRemark", "TeamWorkCategoryId", "WorkTitle", "WorkContent", "WorkStatus", "CreateAdminId", "CreateAdminName", "FinishTime","ReferenceNo","ThreadId" }, new string[] { "TeamWorkId" });
            sql = string.Format("{0} {1} WHERE TeamWorkId=?TeamWorkId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", entity.TeamWorkId, DbType.Int64, size: 20);
            parameters.Add("?FinishAdminId", entity.FinishAdminId, DbType.Int64, size: 20);
            parameters.Add("?FinishAdminName", entity.FinishAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?FinishRemark", entity.FinishRemark?.Trim(), DbType.String);
            parameters.Add("?CloseTime", entity.CloseTime, DbType.DateTime);
            parameters.Add("?CloseAdminId", entity.CloseAdminId, DbType.Int64, size: 20);
            parameters.Add("?CloseAdminName", entity.CloseAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CloseRemark", entity.CloseRemark?.Trim(), DbType.String);
            parameters.Add("?TeamWorkCategoryId", entity.TeamWorkCategoryId, DbType.Int32, size: 11);
            parameters.Add("?WorkTitle", entity.WorkTitle?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?WorkContent", entity.WorkContent?.Trim(), DbType.String);
            parameters.Add("?WorkStatus", entity.WorkStatus.ToString(), DbType.AnsiString);
            parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?FinishTime", entity.FinishTime, DbType.DateTime);
            parameters.Add("?ReferenceNo", entity.ReferenceNo, DbType.AnsiString);
            parameters.Add("?ThreadId", entity.ThreadId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<TeamWorkEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<bool> Delete(long teamWorkId)
        {
            string sql = @"DELETE FROM OA_TeamWork WHERE TeamWorkId=?TeamWorkId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", teamWorkId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<TeamWorkViewEntity> GetEntity(long teamWorkId)
        {
            string sql = string.Format(@"{0} WHERE A.TeamWorkId=?TeamWorkId", QUERY_VIEW);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", teamWorkId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<TeamWorkViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<TeamWorkViewEntity>> GetList(string teamWorkCategoryCode, DateTime? start, DateTime? end)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(teamWorkCategoryCode))
            {
                wheres.Add("B.TeamWorkCategoryCode = ?TeamWorkCategoryCode");
                parameters.Add("?TeamWorkCategoryCode", teamWorkCategoryCode, DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1}",
                QUERY_VIEW,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.databaseContext.Slave.QueryAsync<TeamWorkViewEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<TeamWorkViewEntity>, int)> Query(string referenceNo, string workTitle, string teamWorkCategoryCode, OAWorkStatus? workStatus, DateTime? start, DateTime? end, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(referenceNo))
            {
                wheres.Add("A.ReferenceNo=?ReferenceNo");
                parameters.Add("?ReferenceNo", referenceNo, DbType.AnsiString);
            }
            if (workStatus.HasValue)
            {
                wheres.Add("A.WorkStatus=?WorkStatus");
                parameters.Add("?WorkStatus", workStatus.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(workTitle))
            {
                wheres.Add("A.WorkTitle LIKE CONCAT('%',CONCAT(?WorkTitle,'%'))");
                parameters.Add("?WorkTitle", workTitle?.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(teamWorkCategoryCode))
            {
                wheres.Add("B.TeamWorkCategoryCode=?TeamWorkCategoryCode");
                parameters.Add("?TeamWorkCategoryCode", teamWorkCategoryCode?.Trim(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY A.CreateTime DESC LIMIT {2},{3}; 
                                        SELECT COUNT(1) FROM OA_TeamWork A
                                        INNER JOIN OA_TeamWorkCategory B ON A.TeamWorkCategoryId=B.TeamWorkCategoryId
                                        {1}",
                   QUERY_VIEW,
                   wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                   (pageIndex - 1) * pageSize,
                   pageSize);
            return await this.databaseContext.Slave.QueryMultipleAsync<TeamWorkViewEntity,int>(sql, parameters);
        }

        public async Task<(IEnumerable<TeamWorkViewEntity>, int)> Query(long participantId, string teamWorkCategoryCode, OAWorkStatus? workStatus, DateTime? start, DateTime? end, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            wheres.Add("D.ParticipantId=?ParticipantId");
            parameters.Add("?ParticipantId", participantId, DbType.Int64);
            if (workStatus.HasValue)
            {
                wheres.Add("A.WorkStatus=?WorkStatus");
                parameters.Add("?WorkStatus", workStatus.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(teamWorkCategoryCode))
            {
                wheres.Add("B.TeamWorkCategoryCode=?TeamWorkCategoryCode");
                parameters.Add("?TeamWorkCategoryCode", teamWorkCategoryCode?.Trim(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY A.CreateTime DESC LIMIT {2},{3}; 
                                        SELECT COUNT(1) FROM OA_TeamWork A
                                        INNER JOIN OA_TeamWorkCategory B ON A.TeamWorkCategoryId=B.TeamWorkCategoryId
                                        INNER JOIN OA_TeamWorkParticipant D ON A.TeamWorkId=D.TeamWorkId
                                        {1}",
                   QUERY_PARTICIPANT_VIEW,
                   wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                   (pageIndex - 1) * pageSize,
                   pageSize);
            return await this.databaseContext.Slave.QueryMultipleAsync<TeamWorkViewEntity,int>(sql, parameters);
        }

        public async Task<int> GetProcessingCount(long participantId, string teamWorkCategoryCode)
        {
            string sql = @"SELECT COUNT(1) FROM OA_TeamWork A
                        INNER JOIN OA_TeamWorkCategory B ON A.TeamWorkCategoryId=B.TeamWorkCategoryId
                        INNER JOIN OA_TeamWorkParticipant D ON A.TeamWorkId=D.TeamWorkId
                        WHERE D.ParticipantId=?ParticipantId AND B.TeamWorkCategoryCode=?TeamWorkCategoryCode";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ParticipantId", participantId, DbType.Int64);
            parameters.Add("?TeamWorkCategoryCode", teamWorkCategoryCode?.Trim(), DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters);
        }
    }
}
